/**
 * Copyright 2011 Google Inc.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package com.google.api.adwords.starterkit.examples.enhancedads;

import com.google.api.adwords.starterkit.examples.enhancedads.db.AdGroup2CatId;
import com.google.api.adwords.starterkit.examples.enhancedads.db.AdGroup2ProdId;
import com.google.api.adwords.starterkit.examples.enhancedads.db.Catalog;
import com.google.api.adwords.starterkit.examples.enhancedads.db.Category;
import com.google.api.adwords.starterkit.examples.enhancedads.db.Schedule;
import com.google.api.adwords.starterkit.examples.enhancedads.db.TextAd2CatId;
import com.google.api.adwords.starterkit.examples.enhancedads.db.TextAd2ProdId;

import org.apache.log4j.Logger;

import java.net.MalformedURLException;
import java.net.URL;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * The Class AdWords2DB is responsible to parse all tags information
 * in the destUrl and send it to the correct Tables on DB
 * 
 * @author rodrigo.f.vale@gmail.com
 *  
 */
public class AdWords2DB {
  private Connection conn;
  private Category category;
  private Catalog catalog;
  private Schedule schedule;
  private TextAd2ProdId textAd2ProdId;
  private TextAd2CatId textAd2CatId;
  private AdGroup2ProdId adGroup2ProdId;
  private AdGroup2CatId adGroup2CatId;
  private static final Logger log = Logger.getLogger("StarterKit");
  
  /**
   * Instantiates a new adwords2db.
   *
   * @param conn the connection to StaterKit DB
   */
  public AdWords2DB(Connection conn) {
    this.conn = conn;
    
    category = new Category(conn);
    catalog = new Catalog(conn);
    textAd2ProdId = new TextAd2ProdId(conn); 
    textAd2CatId = new TextAd2CatId(conn);
    schedule = new Schedule(conn);
    adGroup2ProdId = new AdGroup2ProdId(conn); 
    adGroup2CatId = new AdGroup2CatId(conn);
    
    category.clear();
    catalog.clear();
    textAd2ProdId.clear();
    textAd2CatId.clear();
    schedule.clear();
    adGroup2ProdId.clear();
    adGroup2CatId.clear();
  }
  
  /**
   * Extract urls params from destURL and store the values on the correct
   * tables on DB
   */
  public void extractURLsParams() {
    log.info("Extracting URLs Params from TextAds");
    PreparedStatement textAdUrlsQuery;
    URLParams urlParams = new URLParams(); 
    try {
      textAdUrlsQuery = conn.prepareStatement(
          "SELECT LocalId, DestinationUrl, LocalAdGroupId FROM Textads;");
      ResultSet rs = textAdUrlsQuery.executeQuery();
      while (rs.next()) {
        int localId = rs.getInt("LocalId");
        String url = rs.getString("DestinationUrl");
        int localAdGroupId = rs.getInt("LocalAdGroupId");
        try {
          urlParams.extract(new URL(url));          
          if (!urlParams.prodId.equals("")) {
            textAd2ProdId.saveBatch(localId, urlParams.prodId);
          }

          if (!urlParams.catId.equals("")) {
            textAd2CatId.saveBatch(localId, urlParams.catId);
          }
          
          schedule.saveBatch(localId, urlParams.startDate, urlParams.endDate, 
              urlParams.startHour, urlParams.endHour, urlParams.sunday, 
              urlParams.monday, urlParams.tuesday, urlParams.wednesday, 
              urlParams.thursday, urlParams.friday, urlParams.saturday,
              urlParams.altTextAds);
          
          if (!urlParams.prodId.isEmpty() && 
              (!urlParams.adParam1.isEmpty() || 
               !urlParams.adParam2.isEmpty())) {
            adGroup2ProdId.saveBatch(localAdGroupId, urlParams.prodId, 
                urlParams.adParam1, urlParams.adParam1Min, 
                urlParams.adParam1Max, urlParams.adParam2, 
                urlParams.adParam2Min, urlParams.adParam2Max);
          }

          if (!urlParams.catId.isEmpty() && 
              (!urlParams.adParam1.isEmpty() || 
               !urlParams.adParam2.isEmpty())) {
            adGroup2CatId.saveBatch(localAdGroupId, urlParams.catId, 
                urlParams.adParam1, urlParams.adParam1Min, 
                urlParams.adParam1Max, urlParams.adParam2, 
                urlParams.adParam2Min, urlParams.adParam2Max);
          }          
        } catch (MalformedURLException e) {
          e.printStackTrace();
        }
      }
      rs.close();      
      log.debug("Executing the batch update");
      textAd2ProdId.executeBatch();
      textAd2CatId.executeBatch();
      schedule.executeBatch();
      adGroup2ProdId.executeBatch();
      adGroup2CatId.executeBatch();      
    } catch (SQLException e) {
      e.printStackTrace();
    }    
  }
}
